﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;
using System.Windows.Forms;

namespace MarginTrading.Services
{
    public class ClientCategoryServices
    {
        private static ClientCategory GetClientCategoryFromDataReader(IDataReader dataReader)
        {
            ClientCategory retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new ClientCategory();
                retVal.Code = dataReader["CODE"].ToString();
                retVal.Description = dataReader["DESCRIPTION"].ToString();
            }
            return retVal;
        }

        public static ClientCategory GetClientCategoryByCode(string code)
        {
            ClientCategory retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client_category where code = '" + code + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetClientCategoryFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static ClientCategoryCollection GetClientCategoryCollection()
        {
            ClientCategoryCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client_category";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ClientCategoryCollection();
                    while (dataReader.Read())
                    {
                        ClientCategory item = GetClientCategoryFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        //public static AutoCompleteStringCollection GetClientCategoryACCollection()
        //{
        //    AutoCompleteStringCollection retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select CODE from advisor";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            retVal = new AutoCompleteStringCollection();
        //            while (dataReader.Read())
        //            {
        //                string item = dataReader["CODE"].ToString();
        //                if (!string.IsNullOrEmpty(item))
        //                    retVal.Add(item);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static DataTable GetClientCategoryCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from client_category";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static void Add(ClientCategory category)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into client_category(CODE,DESCRIPTION)" +
                                  "values(:v1,:v2)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = category.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = category.Description;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string code)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from client_category where code = '" + code + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(ClientCategory category)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update client_category set " +
                                  "DESCRIPTION = :v2 where CODE = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = category.Code;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = category.Description;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
